package com.aegean.MonitorExamSYS.monitor.dao.impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.aegean.MonitorExamSYS.center.component.SelectExaminee;
import com.aegean.MonitorExamSYS.center.component.SelectPosition;
import com.aegean.MonitorExamSYS.examinee.component.ExamineeExam;
import com.aegean.MonitorExamSYS.monitor.component.ExamPosition;
import com.aegean.MonitorExamSYS.monitor.component.ExamineeState;
import com.aegean.MonitorExamSYS.monitor.dao.MonitorDao;
import com.aegean.MonitorExamSYS.tools.MyTools;
import com.aegean.MonitorExaxSYS.model.BigType;
import com.aegean.MonitorExaxSYS.model.ConductionValue;
import com.aegean.MonitorExaxSYS.model.ExamineeValue;
import com.aegean.MonitorExaxSYS.model.MonitorQualification;
import com.aegean.MonitorExaxSYS.model.MonitorValue;
import com.aegean.MonitorExaxSYS.model.SmallType;
import com.aegean.MonitorExaxSYS.model.component.Photo;

@Repository
@Transactional
public class MonitorDaoImpl implements MonitorDao {
	@Resource
	SessionFactory factory;

	@Override
	public List<BigType> showBigsList() {
		Session session;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(BigType.class);
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<SmallType> showSmallsList(int big_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(SmallType.class);
			cr.add(Restrictions.eq("small_Big.big_Id", big_Id));
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public MonitorValue insertMonitor(MonitorValue monitor, int big_Id,
			int unit_Id) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue one = monitor;
		try {
			System.out.println("big:"+big_Id);
			System.out.println("unit:"+unit_Id);
			session = factory.getCurrentSession();
			if (big_Id != -1 && unit_Id != -1) {
				BigType big = (BigType) session.load(BigType.class, big_Id);
				ConductionValue cv = (ConductionValue) session.load(
						ConductionValue.class, unit_Id);
				one.setMonitor_Big(big);
				one.setMonitor_Unit(cv);
			} else {
				if (big_Id == -1 && unit_Id != -1) {
					ConductionValue cv = (ConductionValue) session.load(
							ConductionValue.class, unit_Id);
					BigType big = (BigType) session.load(BigType.class, 3);//3其他
					one.setMonitor_Big(big);
					one.setMonitor_Unit(cv);
				} else {
					BigType big = (BigType) session.load(BigType.class, big_Id);
					one.setMonitor_Big(big);
				}
			}
			session.save(one);
			return one;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<ConductionValue> showUnitList() {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(ConductionValue.class);
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public MonitorValue loginMonitor(MonitorValue one, int unit_Id) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue monitor;
		try {
			session = factory.getCurrentSession();
			String hql = "from MonitorValue where monitor_Identity=:identity and user_Password=:pwd and monitor_Unit.conduction_Id=:id";
			Query q = session.createQuery(hql);
			q.setString("identity", one.getMonitor_Identity().trim());
			q.setString("pwd", one.getUser_Password().trim());
			q.setInteger("id", unit_Id);
			q.setCacheable(true);
			monitor = (MonitorValue) q.uniqueResult();
			Hibernate.initialize(monitor.getMonitor_Big());
			Hibernate.initialize(monitor.getMonitor_Unit());
			return monitor;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Blob showPhoto(int Customer_Id) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue user;
		Blob photo;
		try {
			session = factory.getCurrentSession();
			user = (MonitorValue) session.get(MonitorValue.class, Customer_Id);
			photo = user.getUser_Photo().getPhoto();
			if (photo != null) {
				return photo;
			} else {
				return null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public MonitorValue updateMonitor(int Customer_Id, MonitorValue one,
			Photo photo) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue user;
		try {
			session = factory.getCurrentSession();
			user = (MonitorValue) session.load(MonitorValue.class, Customer_Id);
			user.setUser_RealName(one.getUser_RealName());
			user.setUser_Sex(one.getUser_Sex());
			user.setMonitor_Email(one.getMonitor_Email());
			user.setMonitor_Phone(one.getMonitor_Phone());
			if (photo == null) {
			} else {
				user.setUser_Photo(photo);
			}
			Hibernate.initialize(user.getMonitor_Big());
			Hibernate.initialize(user.getMonitor_Unit());
			return user;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	@Override
	public MonitorValue updateMonitorPwd(int Customer_Id, String newPwd) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue user;
		try {
			session = factory.getCurrentSession();
			user = (MonitorValue) session.get(MonitorValue.class, Customer_Id);
			user.setUser_Password(newPwd);
			Hibernate.initialize(user.getMonitor_Big());
			Hibernate.initialize(user.getMonitor_Unit());
			return user;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<ExamPosition> showMonitorExams(int monitor_Id, int limit,
			int start) {
		Session session;
		List<ExamPosition> eps = new ArrayList<ExamPosition>();
		try {
			session = factory.getCurrentSession();
			String hql = "select ex.examInfo_Id,ex.examInfo_Name,ex.examInfo_StartTime,ep.examPosition_Id,ep.examPosition_Name,pq.pq_Id,ex.examInfo_EndTime,epq.examPQ_Id,mq.monitorQ_Identity,pq.pq_Identity,small.small_Id,ex.examInfo_Photo.photo_Name from MonitorQualification as mq,PositionQualification as pq,ExamPosition as ep,ExamPositionQualification as epq,ExamInfo as ex,SmallType as small"
					+ " where mq.monitor_User.user_Id=:monitor_Id and mq.monitor_Position=pq and pq.pq_Ep=ep and pq.pq_Epq=epq and epq.examPQ_Exam=ex and small=ex.examInfo_Type"
					+ " order by ex.examInfo_StartTime desc";
			Query q = session.createQuery(hql);
			q.setInteger("monitor_Id", monitor_Id);
			q.setFirstResult(start);
			q.setMaxResults(limit);
			q.setCacheable(true);
			List l = q.list();
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[]) l.get(i);
				ExamPosition ep = new ExamPosition();
				ep.setExam_Id(Integer.parseInt(obj[0].toString().trim()));
				ep.setExam_Name(obj[1].toString().trim());
				ep.setExam_Time(obj[2].toString().trim());
				ep.setPosition_Id(Integer.parseInt(obj[3].toString().trim()));
				ep.setPosition_Name(obj[4].toString().trim());
				ep.setPositionQ_Id(Integer.parseInt(obj[5].toString().trim()));
				ep.setExam_EndTime(obj[6].toString().trim());
				ep.setEpq_Id((Integer.parseInt(obj[7].toString().trim())));
				ep.setIdentity((obj[8].toString().trim()));
				ep.setPosition_Identity(obj[9].toString().trim());
				ep.setSmall_Id(Integer.valueOf(obj[10].toString().trim()));
				ep.setExam_PhotoName(obj[11].toString().trim());
				eps.add(ep);
			}
			return eps;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<com.aegean.MonitorExamSYS.monitor.component.MonitorValue> showMonitorValues(int ep_Id) {
		// TODO Auto-generated method stub
		Session session;
		List<com.aegean.MonitorExamSYS.monitor.component.MonitorValue> monitors=new ArrayList<com.aegean.MonitorExamSYS.monitor.component.MonitorValue>();
		try {
			session = factory.getCurrentSession();
			String hql = "select DISTINCT m.user_Id,m.user_RealName,m.user_Sex,m.monitor_Identity from MonitorValue as m,MonitorQualification as mq,PositionQualification as pq,ExamPositionQualification as epq,BigType as type"
					+ " where mq.monitor_Position=pq and mq.monitor_User=m and pq.pq_Id=:ep_Id and m.monitor_Big=type";
			Query q = session.createQuery(hql);
			q.setInteger("ep_Id", ep_Id);
			q.setCacheable(true);
			List l = q.list();
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[]) l.get(i);
				com.aegean.MonitorExamSYS.monitor.component.MonitorValue one = new com.aegean.MonitorExamSYS.monitor.component.MonitorValue();
				one.setUser_Id(Integer.parseInt(obj[0].toString().trim()));
				one.setUser_RealName(obj[1].toString().trim());
				one.setUser_Sex(obj[2].toString().trim().charAt(0));
				one.setMonitor_Identity(obj[3].toString().trim());
				monitors.add(one);
			}
			return monitors;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int countOfExaminees(int ep_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			String hql = "select count(eq.examinee_Id) from ExamineeQualification as eq,PositionQualification as pq"
					+ " where eq.examineePQ=pq and pq.pq_Id=:ep_Id group by pq.pq_Id";
			Query q = session.createQuery(hql);
			q.setInteger("ep_Id", ep_Id);
			q.setCacheable(true);
			Object ob = q.uniqueResult();
			if(ob==null){
				return 0;
			}
			return Integer.parseInt(ob.toString());
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int showMonitorExamsCount(int monitor_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			String hql = "select ex.examInfo_Id,ex.examInfo_Name,ex.examInfo_StartTime,ep.examPosition_Id,ep.examPosition_Name,pq.pq_Id,ex.examInfo_EndTime,epq.examPQ_Id,mq.monitorQ_Identity from MonitorQualification as mq,PositionQualification as pq,ExamPosition as ep,ExamPositionQualification as epq,ExamInfo as ex"
					+ " where mq.monitor_User.user_Id=:monitor_Id and mq.monitor_Position=pq and pq.pq_Ep=ep and pq.pq_Epq=epq and epq.examPQ_Exam=ex"
					+ " order by ex.examInfo_StartTime desc";
			Query q = session.createQuery(hql);
			q.setInteger("monitor_Id", monitor_Id);
			q.setCacheable(true);
			List l = q.list();
			return l.size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<ExamineeState> showExamineeOnPosition(int position_Id) {
		// TODO Auto-generated method stub
		Session session;
		List<ExamineeState> ones = new ArrayList<ExamineeState>();
		try {
			session = factory.getCurrentSession();
			String hql = "select e.user_Id,e.user_RealName,e.user_Sex,e.examineeValue_IdentityCard,e.examineeValue_Age,eq.examinee_Identity,eq.examinee_State,e.user_Photo.photo_Name,e.examineeValue_Phone,eq.examinee_Id from ExamineeValue as e,ExamineeQualification as eq,PositionQualification as pq where eq.examinee_User=e and eq.examineePQ=pq and pq.pq_Id=:position_Id order by eq.examinee_Identity asc";
			Query q = session.createQuery(hql);
			q.setInteger("position_Id", position_Id);
			q.setCacheable(true);
			List l = q.list();
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[]) l.get(i);
				ExamineeState one = new ExamineeState();
				one.setUser_Id(Integer.parseInt(obj[0].toString().trim()));
				one.setUser_RealName(obj[1].toString().trim());
				one.setUser_Sex(obj[2].toString().trim().charAt(0));
				one.setExamineeValue_IdentityCard(obj[3].toString().trim());
				one.setExamineeValue_Age(Integer.parseInt(obj[4].toString()
						.trim()));
				one.setExam_Identity(obj[5].toString().trim());
				one.setExam_State(Integer.parseInt((obj[6].toString().trim())));
				Photo photo = new Photo();
				photo.setPhoto_Name(obj[7].toString().trim());
				one.setUser_Photo(photo);
				one.setExamineeValue_Phone(obj[8].toString().trim());
				one.setExam_UserID(Integer.valueOf(obj[9].toString().trim()));
				ones.add(one);
			}
			return ones;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Photo> downLoadPhotos(int position_Id) {
		// TODO Auto-generated method stub
		Session session;
		List<Photo> ones = new ArrayList<Photo>();
		Connection conn;
		PreparedStatement ps;
		ResultSet rs;
		try {
			session = factory.getCurrentSession();
			String sql = "SELECT u.USER_ID,u.photo,u.photo_Name,u.photo_Type "
					+ " FROM uservalue as u,examineevalue as e,examineequalification as eq,positionqualification as pq"
					+ " WHERE e.USER_ID=u.USER_ID AND eq.EXAMINEE_USER=e.USER_ID AND eq.EXAMINEEPQ=pq.PQ_ID AND pq.PQ_ID="
					+ position_Id + " ORDER BY eq.EXAMINEE_IDENTITY ASC ";
			conn = session.connection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Photo one = new Photo();
				one.setPhoto_userId(rs.getInt(1));
				one.setPhoto(Hibernate.createBlob(rs.getBinaryStream(2)));
				one.setPhoto_Name(rs.getString(3));
				one.setPhoto_Type(rs.getString(4));
				ones.add(one);
			}
			return ones;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<com.aegean.MonitorExamSYS.monitor.component.MonitorValue> showMonitorOnPosition(int position_Id) {
		// TODO Auto-generated method stub
		Session session;
		List<com.aegean.MonitorExamSYS.monitor.component.MonitorValue> monitors=new ArrayList<com.aegean.MonitorExamSYS.monitor.component.MonitorValue>();
		try {
			System.out.println("position_Id:"+position_Id);
			session = factory.getCurrentSession();
			String hql = "select  m.user_Id,m.user_RealName,m.user_Sex,m.monitor_Identity from MonitorValue as m,MonitorQualification as mq,PositionQualification as pq where mq.monitor_User=m and mq.monitor_Position=pq and pq.pq_Id="
					+ position_Id;
			Query q = session.createQuery(hql);
			q.setCacheable(true);
			List l = q.list();
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[]) l.get(i);
				com.aegean.MonitorExamSYS.monitor.component.MonitorValue one = new com.aegean.MonitorExamSYS.monitor.component.MonitorValue();
				one.setUser_Id(Integer.parseInt(obj[0].toString().trim()));
				one.setUser_RealName(obj[1].toString().trim());
				one.setUser_Sex(obj[2].toString().trim().charAt(0));
				one.setMonitor_Identity(obj[3].toString().trim());
				monitors.add(one);
			}
			return monitors;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}
}
